package game.database.base.shop;

import game.database.common.CommonDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.cs.city.bean.base.shop.ServiceConsume;

/**
 * 
 * @author Linzy
 * Create Date: 2010 3 22 11:40:46
 */
public class ServiceConsumeDB{

	private static final Logger logger = Logger.getLogger(ServiceConsumeDB.class);

	private static String sqlAll = "SELECT id, userID, playerId, serviceType, " +
								"quantity, cost, note, createDate FROM ServiceConsume ";
	
	private static ServiceConsume loadBean(ResultSet rs){
		ServiceConsume bean = new ServiceConsume();
		try {
			bean.setId(rs.getLong("id"));
			bean.setUserId(rs.getLong("userId"));
			bean.setPlayerId(rs.getLong("playerId"));
			bean.setServiceType(rs.getString("serviceType"));
			bean.setQuantity(rs.getInt("quantity"));
			bean.setCost(rs.getInt("cost"));
			bean.setNote(rs.getString("note"));
			bean.setCreateDate(rs.getString("createDate"));
		} catch (Exception e) {
			logger.info("	> ERR in ServiceConsumeDB.loadBean() - " + e.toString());
			logger.error(e.toString());
			e.printStackTrace();
		}
		return bean;
	}
	
	/**
	 * Get a list of bean from database;
	 * 
	 * @param userId
	 * @param playerId
	 * @return
	 */
	public static List<ServiceConsume> getBeanLst(long userId, long playerId){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		ServiceConsume bean = null;
		List<ServiceConsume> beanLst = new ArrayList<ServiceConsume>();
		
		StringBuffer sql = new StringBuffer();
		try {
			sql.append(sqlAll);
			sql.append(" where userId=? and playerId=? ");
			sql.append(" order by createDate ");
			
			conn = CommonDB.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, userId);
			ps.setLong(2, playerId);
			
			rs = ps.executeQuery();
			while (rs.next()) {
				bean = loadBean(rs);
				if(bean != null){
					beanLst.add(bean);
				}
			}
		} catch (Exception e) {
			logger.info("	> ERR in ServiceConsumeDB.getBeanLst() - " + e.toString());
			logger.error(e.toString());
			e.printStackTrace();
		} finally {
			CommonDB.destroyConn(conn, null, ps, rs);
			rs = null;
			ps = null;
		}
		return beanLst;
	} // getBeanLst()
	
	/**
	 * Insert a bean to database;
	 * 
	 * @param bean
	 * @return
	 */
	public static boolean insertBean(ServiceConsume bean){
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ServiceConsume(id, userID,playerID,serviceType," +
					"quantity, cost, note, createDate) ");
		sql.append("VALUES(nextval('SEQ_ServiceConsume'), ?, ?, ?, ?, ?, ?, now())");
		try {
			conn = CommonDB.getConnection();
			ps = conn.prepareStatement(sql.toString());
			
			ps.setLong(1, bean.getUserId());
			ps.setLong(2, bean.getPlayerId());
			ps.setString(3, bean.getServiceType());
			ps.setInt(4, bean.getQuantity());
			ps.setInt(5, bean.getCost());
			ps.setString(6, bean.getNote());
			
			return (ps.executeUpdate() > 0);
		} catch (Exception e) {
			logger.info("	> ERR in ServiceConsumeDB.insertBean() - " + e.toString());
			logger.error(e.toString());
			e.printStackTrace();
		} finally {
			CommonDB.destroyConn(conn, null, ps, null);
			ps = null;
			conn = null;
		}
		return false;
	} // insertBean()


	
} // end of class
